using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using MyClever.Lib;

namespace MyClever.DefaultPlugins
{
    public class UpdateSQLite : MissionPlugin
    {
        [Package(Direction.Input, "Defines the setting for writing or creating the SQLite database file.")]
        enum SQLightDBFile
        {
            [Column(typeof(string), "The SQLite database file, you like edit/create.")]
            FilePath,
            [Column(typeof(string), "The table name, you like to edit/create.")]
            TableName,
            [Column(typeof(string), "The name of the column, which contains an unique identifer for every row, that wasn't changed.")]
            UniqueIdentiferColumnName
        }

        [Package(Direction.Input, "Defines the data, which should overwriten in the database.")]
        enum DBData { }

        [Plugin(PluginDevStatus.Production, "Update all edited data in your favorite table of your expected SQLite database file. If the database/table is not existing, the plugin creates the expected database/table.")]
        public UpdateSQLite()
        {
        }

        public override void Initialize()
        {
            
            //This is the first Function called by the host...
            //Put anything needed to start with here first
        }

        public override void Dispose()
        {
            //Put any cleanup code in here for when the program is stopped
        }

        /// <summary>
        /// Generate new Source-content.
        /// </summary>
        protected override void Work()
        {            
            DataTableReader sqliteDBPackage = this.GetDataReader(typeof(SQLightDBFile));
            while (sqliteDBPackage.Read())
            {
                this.AddNewLoggingMessage(typeof(SQLightDBFile), String.Format("edit table: '{0}' in file: {1}", sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString(), sqliteDBPackage[SQLightDBFile.FilePath.ToString()].ToString()));
                         
                SQLiteConnection connection = new SQLiteConnection();
                connection.ConnectionString = "Data Source=" + sqliteDBPackage[SQLightDBFile.FilePath.ToString()].ToString();
                connection.Open();

                SQLiteCommand command = new SQLiteCommand(connection);

                DataTableReader dbDataPackage = this.GetDataReader(typeof(DBData));
                while (dbDataPackage.Read())
                {
                    string uniqueIdentiferColumn = sqliteDBPackage[SQLightDBFile.UniqueIdentiferColumnName.ToString()].ToString();

                    this.AddNewLoggingMessage(typeof(SQLightDBFile), String.Format("edit data where unique identifer '{0}' = '{1}'", uniqueIdentiferColumn, dbDataPackage[uniqueIdentiferColumn].ToString()));
                    
                    string commandText = "UPDATE " + sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString() + " SET ";
                    foreach (KeyValuePair<string, Type> columnOn in this.GetColumnNames(dbDataPackage))
                    {
                        commandText += columnOn.Key + "='";
                        
                        //SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
                        if (typeof(bool) == columnOn.Value)
                        {
                            commandText += Convert.ToInt32(Convert.ToBoolean(dbDataPackage[columnOn.Key].ToString()));
                        }
                        else
                        {
                            commandText += dbDataPackage[columnOn.Key].ToString();
                        }
                        commandText += "',";
                    }
                    commandText = commandText.Substring(0, commandText.Length - 1);
                    commandText += " WHERE " + uniqueIdentiferColumn + "='" + dbDataPackage[uniqueIdentiferColumn].ToString() + "'";
                    command.CommandText = commandText;
                    command.ExecuteNonQuery();
                }
                connection.Close();
                connection.Dispose();
            }            
        }
    }
}
